//
//  SQLiteUtil.m
//  AlarmClock
//
//  Created by Fedor Skrynnikov on 9/20/12.
//  Copyright (c) 2012 __MyCompanyName__. All rights reserved.
//

#import "SQLiteUtil.h"

@implementation SQLiteUtil

static bool isDBBusy = FALSE;

- (void)dealloc{
    [dbPath release];
    [super dealloc];
}

- (id)init{
    self = [super init];
    if(self){
        NSArray* documentPath = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
        NSString* documentDir = [documentPath objectAtIndex:0];
        dbPath = [[NSMutableString alloc]init];
        [dbPath setString:[documentDir stringByAppendingPathComponent:@"myDB.sql"]];
    }
    return self;
}

- (BOOL)createDBNamedAndDeleteIfExists:(BOOL)toDelete{
    NSFileManager* fileManager = [NSFileManager defaultManager];
    BOOL isDbExists = [fileManager fileExistsAtPath:dbPath];
    if(isDbExists && toDelete){
        NSError* err = nil;
        [fileManager removeItemAtPath:dbPath error:&err];
        NSLog(@"Error while deleting the database: %@", [err localizedDescription] );
        isDbExists = FALSE;
    }
    if (!isDbExists) {
        NSString* dbPathFromApp = [[[NSBundle mainBundle]resourcePath]stringByAppendingPathComponent:@"empty.sql"];
        NSError* err = nil;
        [fileManager copyItemAtPath:dbPathFromApp toPath:dbPath error:&err];
        NSLog(@"Error copying: %@", [err localizedDescription] );
    }
    return !isDbExists;
}


- (void)createTable:(NSString *)tableName{
    [self startTransaction];
    if (sqlite3_open([dbPath UTF8String], &dbSQL) == SQLITE_OK) {
        NSMutableString* statement = [[NSMutableString alloc]initWithString:@"CREATE TABLE "];
        [statement appendString:tableName];
        [statement appendFormat:@" (id INTEGER PRIMARY KEY, name TEXT, note TEXT, recurence TEXT, date TEXT, image BLOB);"];
        
        [self executeSQLStatement:statement];
        [statement release];
    }
    sqlite3_close(dbSQL);
    [self endTransaction];
}

- (void) deleteTable:(NSString *)tableName{
    [self startTransaction];
    if (sqlite3_open([dbPath UTF8String], &dbSQL) == SQLITE_OK) {
        NSMutableString* statement = [[NSMutableString alloc]initWithString:@"DROP TABLE "];
        [statement appendString:tableName];
        [statement appendString:@";"];
        
        [self executeSQLStatement:statement];
        [statement release];
    }
    sqlite3_close(dbSQL);
    [self endTransaction];
}

- (void)insertInTable:(NSString *)tableName Name:(NSString *)name Note:(NSString *)note Recurence:(NSString *)recurence Date:(NSString *)date{
    [self startTransaction];
    if(sqlite3_open([dbPath UTF8String], &dbSQL) == SQLITE_OK){
        NSMutableString* statement = [[NSMutableString alloc]initWithString:@"INSERT INTO "];
        [statement appendString:tableName];
        [statement appendString:@" (name,note,recurence,date,image) VALUES (?,?,?,?,?);"];
        
        sqlite3_stmt* sql_statement = nil;
        const char* sql = [statement UTF8String];
        [statement release];
        if (sqlite3_prepare_v2(dbSQL, sql, -1, &sql_statement, NULL) != SQLITE_OK) {
             NSLog(@"Error in preparing sql statement: %s", sqlite3_errmsg(dbSQL));
        }else {
            sqlite3_bind_text(sql_statement, 1, [name UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_text(sql_statement, 2, [note UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_text(sql_statement, 3, [recurence UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_text(sql_statement, 4, [date UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_blob(sql_statement, 5, nil, -1, SQLITE_TRANSIENT);
            
            if (sqlite3_step(sql_statement) != SQLITE_DONE) {
                 NSLog(@"Error in statement execution: %s", sqlite3_errmsg(dbSQL));
            }
            sqlite3_reset(sql_statement);
            sqlite3_finalize(sql_statement);
        }
    }
    sqlite3_close(dbSQL);
    [self endTransaction];
       
}


- (void)updateImageInTable:(NSString *)tableName WhereId:(NSInteger)recoprdId DataImage:(NSData *)dataImage{
    [self startTransaction];
    if(sqlite3_open([dbPath UTF8String], &dbSQL) == SQLITE_OK){
        NSMutableString* statement = [[NSMutableString alloc] initWithString:@"UPDATE "];
        [statement appendString:tableName];
        [statement appendString:@" SET image=? WHERE id=?;"];
        
        sqlite3_stmt* sql_statement = nil;
        const char* sql = [statement UTF8String];
        [statement release];
        if (sqlite3_prepare_v2(dbSQL, sql, -1, &sql_statement, NULL) != SQLITE_OK) {
            NSLog(@"Error in preparing sql statement: %s", sqlite3_errmsg(dbSQL));
        }else {
            sqlite3_bind_blob(sql_statement, 1, [dataImage bytes], [dataImage length], SQLITE_TRANSIENT);
            sqlite3_bind_int(sql_statement, 2, recoprdId);
        }
        if (sqlite3_step(sql_statement) != SQLITE_DONE){
            NSLog(@"Error in statement execution: %s", sqlite3_errmsg(dbSQL));
        }
        sqlite3_reset(sql_statement);
        sqlite3_finalize(sql_statement);
    }
    sqlite3_close(dbSQL);
    [self endTransaction];
}

- (void)deleteFromTable:(NSString *)tableName record:(NSString *)recordId{
    [self startTransaction];
    if(sqlite3_open([dbPath UTF8String], &dbSQL) == SQLITE_OK){
        NSMutableString* statement = [[NSMutableString alloc] initWithString:@"DELETE from "];
        [statement appendString:tableName];
        [statement appendString:@" WHERE id = "];
        [statement appendString:recordId];
        [statement appendString:@";"];
        
        [self executeSQLStatement:statement];
        [statement release];
    }
    sqlite3_close(dbSQL);
    [self endTransaction];
}

- (NSMutableArray*)selectFromTable:(NSString *)tableName{
    [self startTransaction];
    NSMutableArray* resultSet = nil;
    if(sqlite3_open([dbPath UTF8String], &dbSQL) == SQLITE_OK){
        NSMutableString* statement = [[NSMutableString alloc]initWithString:@"SELECT * FROM "];
        [statement appendString:tableName];
        [statement appendString:@";"];
        
        sqlite3_stmt* sql_statement = nil;
        const char* sql = [statement UTF8String];
        [statement release];
        if (sqlite3_prepare_v2(dbSQL, sql, -1, &sql_statement, NULL) != SQLITE_OK) {
            NSLog(@"Error in preparing sql statement: %s", sqlite3_errmsg(dbSQL));
        }else {
            resultSet = [[NSMutableArray alloc]init];
            while (sqlite3_step(sql_statement) == SQLITE_ROW) {
                NSData* imageData = [[NSData alloc] initWithBytes: sqlite3_column_blob(sql_statement, 5) length:sqlite3_column_bytes(sql_statement, 5)];
                NSString* date = [NSString stringWithUTF8String:(char*)sqlite3_column_text(sql_statement, 4)];
                NSString* recurence = [NSString stringWithUTF8String:(char*)sqlite3_column_text(sql_statement, 3)];
                NSString* note = [NSString stringWithUTF8String:(char*)sqlite3_column_text(sql_statement, 2)];
                NSString* name = [NSString stringWithUTF8String:(char*)sqlite3_column_text(sql_statement, 1)];
                NSString* recordID = [NSString stringWithFormat: @"%d",sqlite3_column_int(sql_statement, 0)];
                NSDictionary * record = [[NSDictionary alloc]initWithObjectsAndKeys:
                                         recordID, @"recordId",
                                         name, @"name",
                                         note, @"note",
                                         recurence, @"recurence", 
                                         date, @"date", 
                                         nil ];
                [imageData release];
                [resultSet addObject:record];
                [record release];
            }
        }
        
    }
    sqlite3_close(dbSQL);
    
    [self endTransaction];
    return resultSet;
}

- (void) executeSQLStatement: (NSMutableString*) statement{
    sqlite3_stmt* sql_statement = nil;
    const char* sql = [statement UTF8String];

    if (sqlite3_prepare_v2(dbSQL, sql, -1, &sql_statement, NULL) != SQLITE_OK) {
        NSLog(@"Error in preparing sql statement: %s", sqlite3_errmsg(dbSQL));
    } else {
        if(sqlite3_step(sql_statement) != SQLITE_DONE){
            NSLog(@"Error in statement execution: %s", sqlite3_errmsg(dbSQL));
        }
        sqlite3_reset(sql_statement);
        sqlite3_finalize(sql_statement);
    }
}

- (void) startTransaction{
    while (isDBBusy) {
        [NSThread sleepForTimeInterval:0.05];
    }
    isDBBusy = TRUE;
}

- (void) endTransaction{
    isDBBusy = FALSE;
}
@end
